﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseData;
using BaseData.Common;
using TaxEntity.Tax;

namespace TaxData.Tax
{
    /// <summary>
    /// 完税记录
    /// </summary>
    public class TaxCompleteData
    {
        /// <summary>
        /// 获取完税信息实体
        /// </summary>
        public TaxCompleteEntity GetEntityById(long id)
        {
            string sql = "select * from TaxComplete with(nolock) where id = @id and status=1";
            SqlParameter p = new SqlParameter("@id", SqlDbType.BigInt, 16);
            p.Value = id;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            TaxCompleteEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 删除完税
        /// </summary>
        public bool Delete(long id)
        {
            //string sql = "update TaxComplete set status=0 where id = @id";
            string sql = "delete from TaxComplete where id = @id";
            SqlParameter p = new SqlParameter("@id", SqlDbType.BigInt, 16);
            p.Value = id;
            int affectRows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql, p);
            return affectRows > 0;
        }

        /// <summary>
        /// 获取完税列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageSize, int page, ref int allCount)
        {
            DataTable dt = new DataTable();
            string condition = " where status=1" + strWhere;
            string sqlCount = "select count(1) from TaxComplete with(nolock) " + condition;
            allCount = (int)DataConfig.GetSingle(BusinessType.DBRead, sqlCount);
            if (allCount > 0)
            {
                int startNum = (page - 1) * pageSize + 1;
                int endNum = page * pageSize;
                string sqlInner = "select *,ROW_NUMBER() over(order by AddTime desc) as RowNumber from TaxComplete a with(nolock) " + condition;
                string sqlUserList = string.Format("SELECT * FROM ({0}) T WHERE T.RowNumber BETWEEN {1} AND {2} order by RowNumber asc", sqlInner, startNum, endNum);
                dt = DataConfig.QueryRecords(BusinessType.DBRead, sqlUserList);
            }
            return dt;
        }

        /// <summary>
        /// 获取完税列表
        /// </summary>
        public DataTable GetList(string certificateID)
        {
            string sql = "select * from TaxComplete with(nolock) where status=1 and certificateID = '" + certificateID + "' order by Year desc,AddTime desc";
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql);
            return dt;
        }

        /// <summary>
        /// 增加完税
        /// </summary>
        /// <param name="model">完税信息实体</param>
        /// <returns>成功完税编号,失败返回0</returns>
        public long Add(TaxCompleteEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.Year != null)
            {
                strSql1.Append("Year,");
                strSql2.Append("" + model.Year + ",");
            }
            if (model.CertificateID != null)
            {
                strSql1.Append("CertificateID,");
                strSql2.Append("'" + model.CertificateID + "',");
            }
            if (model.RentID != null)
            {
                strSql1.Append("RentID,");
                strSql2.Append("" + model.RentID + ",");
            }
            if (model.RentPrice != null)
            {
                strSql1.Append("RentPrice,");
                strSql2.Append("" + model.RentPrice + ",");
            }
            if (model.FangchanArea != null)
            {
                strSql1.Append("FangchanArea,");
                strSql2.Append("" + model.FangchanArea + ",");
            }
            if (model.OriginalPrice != null)
            {
                strSql1.Append("OriginalPrice,");
                strSql2.Append("" + model.OriginalPrice + ",");
            }
            if (model.TudiArea != null)
            {
                strSql1.Append("TudiArea,");
                strSql2.Append("" + model.TudiArea + ",");
            }
            if (model.RemainPrice != null)
            {
                strSql1.Append("RemainPrice,");
                strSql2.Append("" + model.RemainPrice + ",");
            }
            if (model.DiduanID != null)
            {
                strSql1.Append("DiduanID,");
                strSql2.Append("" + model.DiduanID + ",");
            }
            if (model.HouseType != null)
            {
                strSql1.Append("HouseType,");
                strSql2.Append("'" + model.HouseType + "',");
            }
            if (model.IsTaxOK != null)
            {
                strSql1.Append("IsTaxOK,");
                strSql2.Append("" + model.IsTaxOK + ",");
            }
            if (model.ComDate != null)
            {
                strSql1.Append("ComDate,");
                strSql2.Append("'" + model.ComDate + "',");
            }
            if (model.StartDate != null)
            {
                strSql1.Append("StartDate,");
                strSql2.Append("'" + model.StartDate + "',");
            }
            if (model.EndDate != null)
            {
                strSql1.Append("EndDate,");
                strSql2.Append("'" + model.EndDate + "',");
            }
            if (model.OptUserID != null)
            {
                strSql1.Append("OptUserID,");
                strSql2.Append("" + model.OptUserID + ",");
            }
            if (model.TaxInputType != null)
            {
                strSql1.Append("TaxInputType,");
                strSql2.Append("'" + model.TaxInputType + "',");
            }
            if (model.Status != null)
            {
                strSql1.Append("Status,");
                strSql2.Append("" + model.Status + ",");
            }
            if (model.AddTime != null)
            {
                strSql1.Append("AddTime,");
                strSql2.Append("'" + model.AddTime + "',");
            }
            if (model.ModifyTime != null)
            {
                strSql1.Append("ModifyTime,");
                strSql2.Append("'" + model.ModifyTime + "',");
            }
            if (model.TotalTax != null)
            {
                strSql1.Append("TotalTax,");
                strSql2.Append("" + model.TotalTax + ",");
            }
            if (model.YingyeTax != null)
            {
                strSql1.Append("YingyeTax,");
                strSql2.Append("" + model.YingyeTax + ",");
            }
            if (model.PersonalTax != null)
            {
                strSql1.Append("PersonalTax,");
                strSql2.Append("" + model.PersonalTax + ",");
            }
            if (model.CityTax != null)
            {
                strSql1.Append("CityTax,");
                strSql2.Append("" + model.CityTax + ",");
            }
            if (model.JiaoyuTax != null)
            {
                strSql1.Append("JiaoyuTax,");
                strSql2.Append("" + model.JiaoyuTax + ",");
            }
            if (model.DifangJiaoyuTax != null)
            {
                strSql1.Append("DifangJiaoyuTax,");
                strSql2.Append("" + model.DifangJiaoyuTax + ",");
            }
            if (model.YinhuaTax != null)
            {
                strSql1.Append("YinhuaTax,");
                strSql2.Append("" + model.YinhuaTax + ",");
            }
            if (model.FangchanTax != null)
            {
                strSql1.Append("FangchanTax,");
                strSql2.Append("" + model.FangchanTax + ",");
            }
            if (model.TudiTax != null)
            {
                strSql1.Append("TudiTax,");
                strSql2.Append("" + model.TudiTax + ",");
            }
            strSql.Append("insert into TaxComplete(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DataConfig.GetSingle(BusinessType.DBWrite, strSql.ToString());
            long id = 0;
            if (obj != null && obj != DBNull.Value)
            {
                long.TryParse(obj.ToString(), out id);
            }
            return id;
        }

        /// <summary>
        /// 修改完税信息
        /// </summary>
        /// <param name="model">修改完税</param>
        /// <returns>操作结果</returns>
        public bool Update(TaxCompleteEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TaxComplete set ");
            if (model.RentID != null)
            {
                strSql.Append("RentID=" + model.RentID + ",");
            }
            if (model.RentPrice != null)
            {
                strSql.Append("RentPrice=" + model.RentPrice + ",");
            }
            if (model.FangchanArea != null)
            {
                strSql.Append("FangchanArea=" + model.FangchanArea + ",");
            }
            if (model.OriginalPrice != null)
            {
                strSql.Append("OriginalPrice=" + model.OriginalPrice + ",");
            }
            if (model.TudiArea != null)
            {
                strSql.Append("TudiArea=" + model.TudiArea + ",");
            }
            if (model.RemainPrice != null)
            {
                strSql.Append("RemainPrice=" + model.RemainPrice + ",");
            }
            if (model.DiduanID != null)
            {
                strSql.Append("DiduanID=" + model.DiduanID + ",");
            }
            if (model.HouseType != null)
            {
                strSql.Append("HouseType='" + model.HouseType + "',");
            }
            if (model.IsTaxOK != null)
            {
                strSql.Append("IsTaxOK=" + model.IsTaxOK + ",");
            }
            if (model.ComDate != null)
            {
                strSql.Append("ComDate='" + model.ComDate + "',");
            }
            if (model.StartDate != null)
            {
                strSql.Append("StartDate='" + model.StartDate + "',");
            }
            if (model.EndDate != null)
            {
                strSql.Append("EndDate='" + model.EndDate + "',");
            }
            if (model.OptUserID != null)
            {
                strSql.Append("OptUserID=" + model.OptUserID + ",");
            }
            if (model.TaxInputType != null)
            {
                strSql.Append("TaxInputType='" + model.TaxInputType + "',");
            }
            if (model.Status != null)
            {
                strSql.Append("Status=" + model.Status + ",");
            }
            if (model.ModifyTime != null)
            {
                strSql.Append("ModifyTime='" + model.ModifyTime + "',");
            }
            if (model.TotalTax != null)
            {
                strSql.Append("TotalTax=" + model.TotalTax + ",");
            }
            if (model.YingyeTax != null)
            {
                strSql.Append("YingyeTax=" + model.YingyeTax + ",");
            }
            if (model.PersonalTax != null)
            {
                strSql.Append("PersonalTax=" + model.PersonalTax + ",");
            }
            if (model.CityTax != null)
            {
                strSql.Append("CityTax=" + model.CityTax + ",");
            }
            if (model.JiaoyuTax != null)
            {
                strSql.Append("JiaoyuTax=" + model.JiaoyuTax + ",");
            }
            if (model.DifangJiaoyuTax != null)
            {
                strSql.Append("DifangJiaoyuTax=" + model.DifangJiaoyuTax + ",");
            }
            if (model.YinhuaTax != null)
            {
                strSql.Append("YinhuaTax=" + model.YinhuaTax + ",");
            }
            if (model.FangchanTax != null)
            {
                strSql.Append("FangchanTax=" + model.FangchanTax + ",");
            }
            if (model.TudiTax != null)
            {
                strSql.Append("TudiTax=" + model.TudiTax + ",");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.ID);
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, strSql.ToString());
            return rows > 0;
        }

        /// <summary>
        /// 从DataTable中得到一个实体对象
        /// </summary>
        private TaxCompleteEntity GetEntityHelper(DataTable dt)
        {
            TaxCompleteEntity model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new TaxCompleteEntity();

                if (dt.Rows[0]["ID"] != null && dt.Rows[0]["ID"].ToString() != "")
                {
                    model.ID = long.Parse(dt.Rows[0]["ID"].ToString());
                }
                if (dt.Rows[0]["Year"] != null && dt.Rows[0]["Year"].ToString() != "")
                {
                    model.Year = int.Parse(dt.Rows[0]["Year"].ToString());
                }
                if (dt.Rows[0]["CertificateID"] != null && dt.Rows[0]["CertificateID"].ToString() != "")
                {
                    model.CertificateID = dt.Rows[0]["CertificateID"].ToString();
                }
                if (dt.Rows[0]["RentID"] != null && dt.Rows[0]["RentID"].ToString() != "")
                {
                    model.RentID = long.Parse(dt.Rows[0]["RentID"].ToString());
                }
                if (dt.Rows[0]["RentPrice"] != null && dt.Rows[0]["RentPrice"].ToString() != "")
                {
                    model.RentPrice = decimal.Parse(dt.Rows[0]["RentPrice"].ToString());
                }
                if (dt.Rows[0]["FangchanArea"] != null && dt.Rows[0]["FangchanArea"].ToString() != "")
                {
                    model.FangchanArea = decimal.Parse(dt.Rows[0]["FangchanArea"].ToString());
                }
                if (dt.Rows[0]["OriginalPrice"] != null && dt.Rows[0]["OriginalPrice"].ToString() != "")
                {
                    model.OriginalPrice = decimal.Parse(dt.Rows[0]["OriginalPrice"].ToString());
                }
                if (dt.Rows[0]["TudiArea"] != null && dt.Rows[0]["TudiArea"].ToString() != "")
                {
                    model.TudiArea = decimal.Parse(dt.Rows[0]["TudiArea"].ToString());
                }
                if (dt.Rows[0]["RemainPrice"] != null && dt.Rows[0]["RemainPrice"].ToString() != "")
                {
                    model.RemainPrice = decimal.Parse(dt.Rows[0]["RemainPrice"].ToString());
                }
                if (dt.Rows[0]["DiduanID"] != null && dt.Rows[0]["DiduanID"].ToString() != "")
                {
                    model.DiduanID = int.Parse(dt.Rows[0]["DiduanID"].ToString());
                }
                if (dt.Rows[0]["HouseType"] != null && dt.Rows[0]["HouseType"].ToString() != "")
                {
                    model.HouseType = dt.Rows[0]["HouseType"].ToString();
                }
                if (dt.Rows[0]["IsTaxOK"] != null && dt.Rows[0]["IsTaxOK"].ToString() != "")
                {
                    model.IsTaxOK = int.Parse(dt.Rows[0]["IsTaxOK"].ToString());
                }
                if (dt.Rows[0]["ComDate"] != null && dt.Rows[0]["ComDate"].ToString() != "")
                {
                    model.ComDate = DateTime.Parse(dt.Rows[0]["ComDate"].ToString());
                }
                if (dt.Rows[0]["StartDate"] != null && dt.Rows[0]["StartDate"].ToString() != "")
                {
                    model.StartDate = DateTime.Parse(dt.Rows[0]["StartDate"].ToString());
                }
                if (dt.Rows[0]["EndDate"] != null && dt.Rows[0]["EndDate"].ToString() != "")
                {
                    model.EndDate = DateTime.Parse(dt.Rows[0]["EndDate"].ToString());
                }
                if (dt.Rows[0]["OptUserID"] != null && dt.Rows[0]["OptUserID"].ToString() != "")
                {
                    model.OptUserID = long.Parse(dt.Rows[0]["OptUserID"].ToString());
                }
                if (dt.Rows[0]["TaxInputType"] != null && dt.Rows[0]["TaxInputType"].ToString() != "")
                {
                    model.TaxInputType = dt.Rows[0]["TaxInputType"].ToString();
                }
                if (dt.Rows[0]["Status"] != null && dt.Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(dt.Rows[0]["Status"].ToString());
                }
                if (dt.Rows[0]["AddTime"] != null && dt.Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(dt.Rows[0]["AddTime"].ToString());
                }
                if (dt.Rows[0]["ModifyTime"] != null && dt.Rows[0]["ModifyTime"].ToString() != "")
                {
                    model.ModifyTime = DateTime.Parse(dt.Rows[0]["ModifyTime"].ToString());
                }
                if (dt.Rows[0]["TotalTax"] != null && dt.Rows[0]["TotalTax"].ToString() != "")
                {
                    model.TotalTax = decimal.Parse(dt.Rows[0]["TotalTax"].ToString());
                }
                if (dt.Rows[0]["YingyeTax"] != null && dt.Rows[0]["YingyeTax"].ToString() != "")
                {
                    model.YingyeTax = decimal.Parse(dt.Rows[0]["YingyeTax"].ToString());
                }
                if (dt.Rows[0]["PersonalTax"] != null && dt.Rows[0]["PersonalTax"].ToString() != "")
                {
                    model.PersonalTax = decimal.Parse(dt.Rows[0]["PersonalTax"].ToString());
                }
                if (dt.Rows[0]["CityTax"] != null && dt.Rows[0]["CityTax"].ToString() != "")
                {
                    model.CityTax = decimal.Parse(dt.Rows[0]["CityTax"].ToString());
                }
                if (dt.Rows[0]["JiaoyuTax"] != null && dt.Rows[0]["JiaoyuTax"].ToString() != "")
                {
                    model.JiaoyuTax = decimal.Parse(dt.Rows[0]["JiaoyuTax"].ToString());
                }
                if (dt.Rows[0]["DifangJiaoyuTax"] != null && dt.Rows[0]["DifangJiaoyuTax"].ToString() != "")
                {
                    model.DifangJiaoyuTax = decimal.Parse(dt.Rows[0]["DifangJiaoyuTax"].ToString());
                }
                if (dt.Rows[0]["YinhuaTax"] != null && dt.Rows[0]["YinhuaTax"].ToString() != "")
                {
                    model.YinhuaTax = decimal.Parse(dt.Rows[0]["YinhuaTax"].ToString());
                }
                if (dt.Rows[0]["FangchanTax"] != null && dt.Rows[0]["FangchanTax"].ToString() != "")
                {
                    model.FangchanTax = decimal.Parse(dt.Rows[0]["FangchanTax"].ToString());
                }
                if (dt.Rows[0]["TudiTax"] != null && dt.Rows[0]["TudiTax"].ToString() != "")
                {
                    model.TudiTax = decimal.Parse(dt.Rows[0]["TudiTax"].ToString());
                }
            }
            return model;
        }
    }
}
